﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
/// <summary>
/// UserHelper 的摘要说明
/// </summary>
public class UserHelper
{
    private string _Name;
    private string _Password;
    public UserHelper()
    {
        _Name = null;
        _Password = null;
    }
    public UserHelper(string name, string password)
    {
        this._Name = name;
        this._Password = mdfive.MD5(password);
    }
    public DataSet GetTopicListInfo(int start,int end,int Key)
    {

        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetTopicList";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@startIndex",start));
        com.Parameters.Add(new SqlParameter("@endIndex", end));
        com.Parameters.Add(new SqlParameter("@topicclass", Key));
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds;
    }
    /// <summary>
    /// 获取帖子总数
    /// </summary>
    /// <param name="Key"></param>
    /// <returns></returns>
    public int GetTopicCount(int Key)
    {
        int num=0;
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetTopicNum";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@TopicClass", Key));
        con.Open();
        SqlDataReader dr = com.ExecuteReader();
        while (dr.Read())
        {
            num = Convert.ToInt32(dr[0]);
        }
        return num;
    }
    public string GetTopicClass(int Key)
    {
        string retu = null;
        switch (Key)
        {
            case 1:
                retu="律师文集";
                break;
            case 2:
                retu="案例分析";
                break;
            case 3:
                retu = "法律解答";
                break;
            case 4:
                retu = "律师实务";
                break;
            default:
                retu = null;
                break;
        }
        return retu;
    }
    public DataSet GetTopicContent(int Key)
    {
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetTopicContent";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@TopicID", Key));
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds;
    }
    public bool Login()
    {
        int a = 0;
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "Login";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@UserName", this._Name));
        com.Parameters.Add(new SqlParameter("@UserPassword", this._Password));
        con.Open();
        SqlDataReader dr = com.ExecuteReader();
        
        while (dr.Read())
        {
            a = Convert.ToInt32(dr[0]);
        }
        con.Close();
        if (a == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    public bool AddNum(int ID)
    {
        int a = 0;
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "updatenum";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@ID", ID));
        con.Open();
        a = com.ExecuteNonQuery();
        if (a > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
        
    }
    /// <summary>
    /// 发布贴子
    /// </summary>
    /// <param name="title">标题</param>
    /// <param name="Content">内容</param>
    /// <param name="name">用户名</param>
    /// <param name="Class">贴子类型</param>
    /// <param name="Num">浏览数量</param>
    /// <returns>返回发布是否成功</returns>
    public bool AdminPublish(string title, string Content,string name,int Class,int Num)
    {
        int a = 0;
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "Publish";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@TopicTitle", title));
        com.Parameters.Add(new SqlParameter("@TopicClass", Class));
        com.Parameters.Add(new SqlParameter("@TopicLookNum", Num));
        com.Parameters.Add(new SqlParameter("@content", Content));
        com.Parameters.Add(new SqlParameter("@UserName",name));
        con.Open();
        SqlDataReader dr = com.ExecuteReader();
        while (dr.Read())
        {
            a = Convert.ToInt32(dr[0]);
        }
        con.Close();
        if (a > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    public DataSet GetTopicInfo(int start,int end)
    {
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "AdminGettopic";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@startIndex",start));
        com.Parameters.Add(new SqlParameter("@endIndex", end));
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds;
    }
    public int GetTopicC()
    {
        int num = 0;
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetTopicCount";
        com.CommandType = CommandType.StoredProcedure;
        con.Open();
        SqlDataReader dr = com.ExecuteReader();
        while (dr.Read())
        {
            num = Convert.ToInt32(dr[0]);
        }
        return num;
    }
    public bool DeleteTopic(int ID)
    {
         SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "DeleteTopic";
        com.CommandType = CommandType.StoredProcedure;
        con.Open();
        com.Parameters.Add(new SqlParameter("@ID",ID));
        int n = com.ExecuteNonQuery();
        con.Close();
        if (n == 2)
        {
            return true;
        }
        else
        {
            return false;
        }
        

    }
    public DataSet GetIndexInfo(int TopicClsss)
    {
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetIndexInfo";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@TopicClass", TopicClsss));
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds;
    }
    /// <summary>
    /// 首页获取留言信息
    /// </summary>
    /// <returns></returns>
    public DataSet Getliuyan()
    {
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "Getliuyan";
        com.CommandType = CommandType.StoredProcedure;
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds;
    }
    public DataSet GetLeaveMessage(int start,int end)
    {
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetLeaveMessage";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@startIndex", start));
        com.Parameters.Add(new SqlParameter("@endIndex", end));
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds;
    }
    public int GetLeaveMessageCount()
    {
        int a = 0;
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetLeaveMessageCount";
        com.CommandType = CommandType.StoredProcedure;
        con.Open();
        SqlDataReader dr = com.ExecuteReader();
        while (dr.Read())
        {
            a = Convert.ToInt32(dr[0]);
        }
        con.Close();
        return a;
    }
    public bool DeleteMessage(int index)
    {
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "DeleteMessage";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@ID", index));
        con.Open();
        int n = com.ExecuteNonQuery();
        con.Close();
        if (n > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    public string ContentForID(int ID)
    {
        LeaveReply lr = new LeaveReply();
        JavaScriptSerializer serializer = new JavaScriptSerializer();
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "ContentForID";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@ID", ID));
        con.Open();
        SqlDataReader dr = com.ExecuteReader();
        while (dr.Read())
        {
            lr.Content = dr[0].ToString();
            lr.Reply = dr[1].ToString();
        }
        con.Close();
        return serializer.Serialize(lr); ;
    }
    /// <summary>
    /// 更新回复状态
    /// </summary>
    /// <param name="ID">留言贴ID</param>
    /// <param name="Reply">回复内容</param>
    /// <returns></returns>
    public bool UpDateMessageContent(int ID, string Reply)
    {
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "UpDateMessageContent";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@ID", ID));
        com.Parameters.Add(new SqlParameter("@Reply", Reply));
        con.Open();
        int n = com.ExecuteNonQuery();
        con.Close();
        if (n > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
        
    }
    public string FindAdminAddress(string UserIP,string User)
    {
        string s = null;
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "FindAdminAddress";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@UserIP", UserIP));
        com.Parameters.Add(new SqlParameter("@username", User));
        con.Open();
        SqlDataReader dr = com.ExecuteReader();
        while (dr.Read())
        {
            s = dr[0].ToString();
        }
        con.Close();
        if (s!="")
        {
            return s;
        }
        else
        {
            return null;
        }
    }
    public DataSet GetAlbumList(int ID, int end, int start)
    {
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetAlbumList";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@Class", ID));
        com.Parameters.Add(new SqlParameter("@startIndex", start));
        com.Parameters.Add(new SqlParameter("@endIndex", end));
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds;
    }
    public int GetAlbumListCount(int AlbumClass)
    {
        int a = 0;
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetAlbumListCount";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@Class", AlbumClass));
        con.Open();
        SqlDataReader dr = com.ExecuteReader();
        while (dr.Read())
        {
            a = Convert.ToInt32(dr[0]);
        }
        con.Close();
        return a;
    }
    public DataSet GetHotTopic()
    {
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetHotTopic";
        com.CommandType = CommandType.StoredProcedure;
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds;
    }
    /// <summary>
    /// 获取图片文件名最大数
    /// </summary>
    /// <returns></returns>
    public string GetImgNameMaxNum(int Class)
    {
        string Num = null;
        SqlConnection con = new SqlConnection(DbHelper.strCon);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "GetImgNameMaxNum";
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter sp = new SqlParameter("@class",SqlDbType.Int);
        sp.Value = Class;
        com.Parameters.Add(sp);
        SqlParameter Ret = new SqlParameter("@ret", SqlDbType.Int, 4);
        Ret.Direction = ParameterDirection.ReturnValue;
        com.Parameters.Add(Ret);
        con.Open();
        com.ExecuteNonQuery();
        Num = Ret.Value.ToString();
        con.Close();
        return Num;
    }
}